using System;
using System.Collections.Generic;
using System.Data;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class NhanvienDao:IDaoObject<NhanVienDto, int>
    {
        #region IDaoObject<NhanvienDTO,int> Members

        public NhanVienDto SaveOrUpdate(NhanVienDto obj)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection connection = MyConnection.ConnectionData();
            //Make transaction for saving bill
            OleDbTransaction transaction = connection.BeginTransaction();
            try
            {
                transaction.Begin();
                string sqlPattern;
                if (obj.Id == 0)
                {
                    //Insert INTO nhanvien table
                    sqlPattern = "insert into Nhanvien(Manhanvien, Username, Password, Tennhanvien, Dienthoai, Diachi, CMND, Maphanquyen)" +
                               " values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7})'";
                }
                else
                {
                    sqlPattern = "Update Nhanvien set Manhanvien='{0}', set Username='{1}', set Password='{2}', set Tennhanvien='{3}', set Dienthoai='{4}', set Diachi='{5}', set CMND='{6}', set Maphanquyen='{7}'";
                }
                 string sqlString = String.Format(sqlPattern, obj.MaNhanVien, obj.UserName, obj.PassWord, obj.TenNhanVien, obj.DienThoai, obj.DiaChi, obj.CMND, obj.PhanQuyen.Id);
                OleDbCommand cmd = new OleDbCommand(sqlString, connection);
                obj.Id = cmd.ExecuteNonQuery();
                transaction.Commit();
                return obj;
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            return null;
        }

        public NhanVienDto SaveOrUpdate(NhanVienDto obj, OleDbTransaction transaction)
        {
            throw new NotImplementedException();
        }

        public void Delete(int id)
        {
            //throw new Exception("The method or operation is not implemented.");

            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "Delete From Nhanvien Where ID='" + id + "'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            //cmd.Parameters.Add("@Ma", OleDbType.Integer);
            //cmd.Parameters["@Ma"].Value = manv;
            cmd.ExecuteNonQuery();
            cn.Close();

        }

        public NhanVienDto Select(int id)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "select * from Nhanvien nv,Phanquyen pq where nv.Maphanquyen = pq.Id and ID='"+id+"'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            NhanVienDto nv = new NhanVienDto();
            while (dr.Read())
            {
                
                nv.Id=Convert.ToInt32(dr["ID"].ToString());
                nv.MaNhanVien=dr["Manhanvien"].ToString();
                nv.PassWord=dr["Password"].ToString();
                nv.TenNhanVien=dr["Tennhanvien"].ToString();
                nv.UserName=dr["Username"].ToString();
                nv.DiaChi = dr["Diachi"].ToString();
                nv.DienThoai = dr["Dienthoai"].ToString();
                nv.CMND = dr["CMND"].ToString();
                PhanQuyenDto quyenDto = new PhanQuyenDto();
                quyenDto.Id = Convert.ToInt32(dr["Maphanquyen"].ToString());
                quyenDto.TenPhanQuyen = (string) dr["Tenphanquyen"];
                nv.PhanQuyen = quyenDto;
            }
            dr.Close();
            cn.Close();
            return nv;

        }

        public NhanVienDto Select(string username, string password)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            const string strPattern = "select nv.*, pq.Tenphanquyen from Nhanvien nv,Phanquyen pq where nv.Maphanquyen = pq.Id and Username='{0}' and Password='{1}'";
            string strSql = string.Format(strPattern, username, password);
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);

            NhanVienDto nv = new NhanVienDto();
            if(dataTable.Rows.Count > 0)
            {
                DataRow dr = dataTable.Rows[0];
                nv.Id = Convert.ToInt32(dr["ID"].ToString());
                nv.MaNhanVien = dr["Manhanvien"].ToString();
                nv.PassWord = dr["Password"].ToString();
                nv.TenNhanVien = dr["Tennhanvien"].ToString();
                nv.UserName = dr["Username"].ToString();
                nv.DiaChi = dr["Diachi"].ToString();
                nv.DienThoai = dr["Dienthoai"].ToString();
                nv.CMND = dr["CMND"].ToString();
                PhanQuyenDto quyenDto = new PhanQuyenDto();
                quyenDto.Id = Convert.ToInt32(dr["Maphanquyen"].ToString());
                quyenDto.TenPhanQuyen = (string)dr["Tenphanquyen"];
                nv.PhanQuyen = quyenDto;
                return nv;
            }
            else
            {
                return null;
            }
            
            
        }

        public NhanVienDto[] SelectAll()
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "select * from Nhanvien nv,Phanquyen pq where nv.Maphanquyen = pq.Id";
            OleDbDataAdapter dta = new OleDbDataAdapter(strSQL,cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List <NhanVienDto> nvdto = new List <NhanVienDto>();

            foreach (DataRow dr in dataTable.Rows)
            {
                NhanVienDto nv = new NhanVienDto();
                nv.Id = Convert.ToInt32(dr["ID"].ToString());
                nv.MaNhanVien = dr["Manhanvien"].ToString();
                nv.PassWord = dr["Password"].ToString();
                nv.TenNhanVien = dr["Tennhanvien"].ToString();
                nv.UserName = dr["Username"].ToString();

                PhanQuyenDto quyenDto = new PhanQuyenDto();
                quyenDto.Id = Convert.ToInt32(dr["Maphanquyen"].ToString());
                quyenDto.TenPhanQuyen = (string)dr["Tenphanquyen"];
                nv.PhanQuyen = quyenDto;

                nv.DiaChi = dr["Diachi"].ToString();
                nv.DienThoai = dr["Dienthoai"].ToString();
                nv.CMND = dr["CMND"].ToString();
                nvdto.Add(nv);
            }
            cn.Close();
            return nvdto.ToArray();
        }

        #endregion
    }
}
